Option Explicit On
'Option Strict

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.sqlclient
'Imports CrystalDecisions.CrystalReports.Engine
'Imports CrystalDecisions.Shared

Public Class DBConnect
    ' connection string for SSH tunneling (working from home)
    'Public SqlConnectString As String = "server=127.0.0.1,5555;Database=CIS4396S03;User id=usrCIS4396S03;Password=BaT58teR"

    Public SqlConnectString As String = "server=pre-stem.temple.edu;Database=CIS4396S03;User id=usrCIS4396S03;Password=BaT58teR"
    'Public SqlConnectString As String = "server=np-stem.temple.edu;Database=CIS4396S03;User id=usrCIS4396S03;Password=BaT58teR"
    Dim myConnectionSql As New SqlConnection(SqlConnectString)
    Dim objCmd As SqlCommand
    Dim objReader As SqlDataReader
    Dim ds As DataSet

    Public Sub New()
        myConnectionSql.Open()
    End Sub

    Public Function getDataSet(ByVal SqlSelect As String) As DataSet
        'InParam is a Select statement. Return is the Dataset 
        'Note: The Dataset is also stored as a class variable for use 
        'in the getField function
        Dim myDataAdapter As New SqlDataAdapter(SqlSelect, myConnectionSql)
        Dim myDataSet As New DataSet()
        myDataAdapter.Fill(myDataSet)
        ds = myDataSet
        Return myDataSet
    End Function

    Public Function getDataSet(ByVal SqlSelect As String, ByRef theCount As Integer) As DataSet
        'InParam is a Select statement, OutParam is the number of rows 
        'in the returned dataset, Return is a Dataset
        Dim myDataAdapter As New SqlDataAdapter(SqlSelect, myConnectionSql)
        Dim myDataSet As New DataSet
        myDataAdapter.Fill(myDataSet)
        ds = myDataSet
        theCount = ds.Tables(0).Rows.Count()
        Return myDataSet
    End Function

    Public Function getDataSet(ByVal SqlSelect As String, ByRef theCount As Integer, ByRef theErrNum As Integer, ByRef theErrMessage As String) As DataSet
        'InParam is a Select statement, OutParams are (1)the number of rows 
        'in the returned dataset, (2)the error number.  If no exception occurs it is zero, 
        'else it is the error number, (3) the error message if an exception occurs.  
        'Returns a Dataset
        Try
            'Dim myDataAdapter As New OleDbDataAdapter(SqlSelect, myConnection)
            Dim myDataAdapter As New SqlDataAdapter(SqlSelect, myConnectionSql)
            Dim myDataSet As New DataSet
            myDataAdapter.Fill(myDataSet)
            ds = myDataSet
            theCount = ds.Tables(0).Rows.Count()
            theErrNum = 0
            Return myDataSet
        Catch e As Exception
            theErrNum = Err.Number
            theErrMessage = e.Message
            Return Nothing
        End Try
    End Function

    Public Function getDataReader(ByVal SqlSelect As String) As SqlDataReader
        'InParam is a Select statement. Returns DataReader
        objCmd = New SqlCommand(SqlSelect, myConnectionSql)
        Return objCmd.ExecuteReader()
    End Function

    Public Function doUpdate(ByVal SqlManipulate As String) As Integer
        'InParam is a SQL manipulate statement (Insert, Update, Delete). 
        'Returns the number of rows affected by the update.
        'If an Exception occurs, -1 is returned
        objCmd = New SqlCommand(SqlManipulate, myConnectionSql)
        Try
            Return objCmd.ExecuteNonQuery()
        Catch e As Exception
            Return -1
        End Try
    End Function

    Public Function doUpdateUsingCmdObj(ByVal aCmdObj As SqlCommand) As Integer
        'InParam is a Command object containing a SQL manipulate 
        'statement (Insert, Update, Delete). 
        'Returns the number of rows affected by the update.
        'If an Exception occurs, -1 is returned
        'This function is used for passing parameters to a Stored Procedure
        Try
            aCmdObj.Connection = myConnectionSql
            Return aCmdObj.ExecuteNonQuery()
        Catch ex As Exception
            Return -1
        End Try
    End Function

    Public Function getDataSetUsingCmdObj(ByVal aCmdObj As SqlCommand) As DataSet
        'Used for Stored Procedures (Select only) with Parameters      
        aCmdObj.Connection = myConnectionSql
        Dim myDataAdapter As New SqlDataAdapter(aCmdObj)
        Dim myDataSet As New DataSet
        myDataAdapter.Fill(myDataSet)
        ds = myDataSet
        Return myDataSet
    End Function

    Public Function getRow(ByVal ds As DataSet, ByVal row As Integer) As DataRow
        Dim objRow As DataRow
        Dim objTable As DataTable = ds.Tables(0)
        objRow = objTable.Rows(row)
        Return objRow
    End Function

    Public Function getRows(ByVal Condition As String) As Array
        'InParams are (1)a DataSet and (2) the zero based row of the 
        'table in the DataSet to be returned.  Returns a row.
        Dim objRow() As DataRow
        Dim objTable As DataTable = ds.Tables(0)
        objRow = objTable.Select(Condition)
        Return objRow
    End Function

    Public Function getField(ByVal FieldName As String, ByVal row As Integer) As Object
        'InParams are (1)a Field (Column) name as a string and
        '(2)the zero based row of the table in the DataSet 
        'from which the field is to be extracted. Returns the value 
        'in the field as a variant type.
        'This function assumes that one of the getDataSet functions
        'had been called, thus producing a ds at the class level.
        Dim objRow As DataRow
        Dim objTable As DataTable = ds.Tables(0)
        objRow = objTable.Rows(row)
        Return objRow(FieldName)
    End Function

    Public Sub commitDataSet(ByVal ds As DataSet)
        'InParam is a DataSet. This function is used to Commit
        'the Dataset to the Data Source when updating a disconnected ds. 
        Dim myDataAdapter As New SqlDataAdapter
        myDataAdapter.Update(ds)
    End Sub

    Public Function ExecScalarFunction(ByVal acmdObj As SqlCommand) As VariantType
        'InParam is a Command object containing a Select statement 
        'that returns a single scalar value. Returns the single scalar value.  
        'Returns  scalar value as a Variant Type.
        acmdObj.Connection = myConnectionSql
        Return CType(acmdObj.ExecuteScalar(), VariantType)
    End Function

    Public Function getConnection() As SqlConnection
        'NOTE: .NET has implemented its Stored User Defined Functions only 
        'With the Managed Provider for SQL Server, 
        'not the OLEDB provider. 
        Return myConnectionSql
    End Function

    Public Sub closeConSql()
        myConnectionSql.Close()
    End Sub

    Public Sub resetConnection()
        myConnectionSql.Close()
        myConnectionSql.Open()
    End Sub

    'Protected Overrides Sub Finalize()
    '    myConnection.Close()
    'End Sub
End Class
